package com.example.xianzhi.dao.impl;

import com.example.xianzhi.dao.GoodsDao;
import com.example.xianzhi.entity.TbAdSmall;
import com.example.xianzhi.entity.TbGoods;
import com.example.xianzhi.util.ORMUtil;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {
    @Override
    public int insertGoods(TbGoods goods) {
        String sql=" INSERT INTO tb_goods(goods_name,goods_picture,goods_price,goods_old_price,goods_describe,release_id,type_id,release_time) \n" +
                " VALUES(?,?,?,?,?,?,?,?)";
        int i = new ORMUtil().executeDML(sql, goods.getGoods_name(), goods.getGoods_picture(), goods.getGoods_price(), goods.getGoods_old_price(), goods.getGoods_describe(), goods.getRelease_id(), goods.getType_id(), goods.getRelease_time());
        return i;
    }

    @Override
    public TbGoods selectIdGoods(int goods_id) {
        String sql=" SELECT * FROM tb_goods WHERE goods_id=?";
        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql, goods_id);

        return tbGoods.size()>0 ? tbGoods.get(0) : null;
    }

    @Override
    public List<TbAdSmall> selectGoodsPic(int goods_id) {
        String sql=" SELECT * FROM tb_ad_small WHERE goods_id=?";
        List<TbAdSmall> TbAdSmall = new ORMUtil().selectList(TbAdSmall.class, sql, goods_id);

        return TbAdSmall;
    }

    @Override
    public List<TbGoods> selectReleaseOrder(int release_id) {
        String sql=" SELECT * FROM tb_goods WHERE (goods_state =1 or goods_state =2)  AND release_id=?\n" +
                " ORDER BY release_time";
        List<TbGoods> tbGoodsorder = new ORMUtil().selectList(TbGoods.class, sql, release_id);
        return tbGoodsorder;
    }

    @Override
    public List<TbGoods> selectOrder(String goods_state,int release_id) {
        String sql="SELECT * FROM tb_goods WHERE goods_state =? AND release_id=?\n" +
                " ORDER BY release_time";
        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql, goods_state, release_id);

        return tbGoods;
    }

    @Override
    public List<TbGoods> selectGoods() {
        String sql=" \n" +
                " SELECT a1.*,a2.type_name FROM tb_goods a1\n" +
                " INNER JOIN tb_type a2 ON a1.type_id=a2.type_id " +
                "where goods_state=1";
        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql);
        return tbGoods;
    }

    @Override
    public List<TbGoods> selectIndent() {
        String sql = "SELECT t2.goods_id,t2.goods_old_price,t2.buy_time,t1.wx_nick addressee,t2.wx_nick publisher,t3.add_name,t2.goods_state,t2.goods_name FROM  \n" +
                "(\n" +
                "\tSELECT tb_goods.*,tb_user.wx_nick,tb_user.wx_address FROM `tb_goods` , tb_user \n" +
                "\tWHERE tb_goods.release_id =tb_user.user_id\n" +
                ")t2,\n" +
                "tb_user t1 ,tb_add t3\n" +
                "WHERE t1.user_id=t2.buyer_id AND t2.goods_add_id=t3.add_id\n" +
                "AND t2.goods_state !=1\n"+
                " AND t2.goods_state !=2";
        return new  ORMUtil().selectList(TbGoods.class,sql);
    }

    @Override
    public void delectGoods(int goods_id) {
        String sql ="\n" +
                "DELETE FROM tb_goods \n" +
                "WHERE goods_id=?";
        new  ORMUtil().executeDML(sql,goods_id);
    }

    @Override
    public List<TbGoods> selectIndentClassification(String publisher, String buy_time, String addressee, int goods_id) {
        String sql = "SELECT t2.goods_id,t2.goods_old_price,t2.buy_time,t1.wx_nick addressee,t2.wx_nick publisher,t3.add_name,t2.goods_state,t2.goods_name FROM  \n" +
                "(\n" +
                "\tSELECT tb_goods.*,tb_user.wx_nick,tb_user.wx_address FROM `tb_goods` , tb_user \n" +
                "\tWHERE tb_goods.release_id =tb_user.user_id\n" +
                ")t2,\n" +
                "tb_user t1 ,tb_add t3\n" +
                "WHERE t1.user_id=t2.buyer_id AND t2.goods_add_id=t3.add_id\n" +
                "AND t2.goods_state !=1\n"+
                " AND t2.goods_state !=2";
        //动态sql的条件
        //创建一个集合存姓名和性别
        List<Object> arraylist = new ArrayList<Object>();

        if (publisher!=null && !publisher.equals("")) {
            sql+=" AND t2.wx_nick LIKE ? ";
            arraylist.add("%"+publisher+"%");
        }

        if (buy_time!=null && !buy_time.equals("")) {
            sql+=" AND t2.buy_time LIKE ? ";
            arraylist.add("%"+buy_time+"%");
        }

        if (addressee!=null && !addressee.equals("")) {
            sql+=" AND t1.wx_nick LIKE  ? ";
            arraylist.add("%"+addressee+"%");
        }

        if (goods_id!=0) {
            sql+=" AND t2.goods_state =? ";
            arraylist.add(goods_id);
        }

        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql, arraylist.toArray());



        return tbGoods;
    }

    @Override
    public List<TbGoods> selectGoodsByGoodsId(int goods_id) {
        String sql="SELECT * FROM `tb_goods`,tb_user\n" +
                "WHERE tb_goods.release_id=tb_user.user_id"+
                " AND tb_goods.goods_id=?";

        return   new  ORMUtil().selectList(TbGoods.class,sql,goods_id);
    }

    @Override
    public void updateGoodsid(int buyer_id, Date buy_time,  int goods_add_id ,int goods_id) {
        String sql ="update tb_goods set buyer_id=?, buy_time=?, goods_state=2 , goods_add_id=?  where goods_id=?";
        new  ORMUtil().executeDML(sql,buyer_id,buy_time,goods_add_id,goods_id);
    }

    @Override
    public List<TbGoods> selectGoodsId(String goods_name, int type_id) {
        String sql="SELECT * FROM tb_goods WHERE goods_name=? AND type_id=?";
        List<TbGoods> list = new ORMUtil().selectList(TbGoods.class, sql, goods_name, type_id);
        return list;
    }

    @Override
    public List<TbGoods> selectGoodsname(String goods_name) {
        String sql="SELECT * FROM tb_goods WHERE goods_state='1' AND  goods_name LIKE ? ";
        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql, "%"+goods_name+"%");
        System.out.println(sql);
        System.out.println(">>>>>>"+tbGoods);
        return tbGoods;
    }

    @Override
    public List<TbGoods> selectGoodsinfo(String goods_name, int type_id) {
        //要注意空格
        String sql=" SELECT a1.*,a2.type_name FROM tb_goods a1\n" +
                " INNER JOIN tb_type a2 ON a1.type_id=a2.type_id \n" +
                " WHERE 1=1 AND"+
                " goods_state=1";
        //动态sql的条件
        //创建一个集合存姓名和性别
        List<Object> arraylist = new ArrayList<Object>();

        if (goods_name!=null && !goods_name.equals("")) {
            sql+=" AND goods_name LIKE ? ";
            arraylist.add("%"+goods_name+"%");
        }

        if (type_id!=0) {
            sql+=" AND a1.type_id=? ";
            arraylist.add(type_id);
        }

        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql, arraylist.toArray());

        return tbGoods;
    }

    @Override
    public int deleteGoods(int goods_id) {

        String sql=" DELETE FROM tb_goods WHERE goods_id=?";
        int i = new ORMUtil().executeDML(sql, goods_id);
        return i;
    }

    @Override
    public List<TbGoods> selectTypeGoods(int type_id) {

        String sql="SELECT goods_id,goods_name,goods_price,goods_old_price FROM tb_goods WHERE type_id=?";

        List<TbGoods> tbGoods = new ORMUtil().selectList(TbGoods.class, sql, type_id);

        return tbGoods;
    }




    @Override//xcc
    public List<TbGoods> selegooodsrela(int user_id) {
        String sql ="select tt4.relation_id,tt4.wx_nick,tt4.wx_head,tt4.user_credit,tt3.* from tb_goods tt3,(select t1.relation_id,t1.goods_id,t1.user_id,t2.wx_nick,t2.wx_head,t2.user_credit from tb_relation t1,\n" +
                "(select user_id,wx_nick,wx_head,user_credit from tb_user WHERE user_id=?) t2\n" +
                "  WHERE t1.user_id = t2.user_id and need =1) tt4  WHERE tt3.goods_id=tt4.goods_id";

        List<TbGoods> goodsrela = new ORMUtil().selectList(TbGoods.class,sql,user_id);
        return goodsrela.size()>0 ? goodsrela : null;
    }

    @Override//xcc
    public List<TbGoods> selegooodsrhist(int user_id) {
        String sql ="select tt4.history_id,tt4.browse_time,tt3.* from tb_goods tt3,(select history_id,browse_time,goods_id from tb_history \n" +
                " where user_id=?) tt4 where tt3.goods_id=tt4.goods_id";

        List<TbGoods> goodshist = new ORMUtil().selectList(TbGoods.class,sql,user_id);
        return goodshist.size()>0 ? goodshist : null;
    }


    @Override//xcctype_id xcc（吴小林）
    public List<TbGoods> selecttypeidgoods(int type_id) {
        String sql = "select t1.wx_nick,t1.wx_head,t1.user_credit,t2.* from tb_user t1,(select *  from tb_goods where goods_state=1 and type_id=? ) t2  \n" +
                " where  t1.user_id = t2.release_id";

        return new  ORMUtil().selectList(TbGoods.class,sql,type_id);
    }

    @Override//xcc（吴小林）
    public List<TbGoods> selectusergoods() {
        String sql = "select t1.wx_nick,t1.wx_head,t1.user_credit,t2.* from tb_user t1,(select *  from tb_goods where goods_state=1 ) t2  \n" +
                " where  t1.user_id = t2.release_id";

        return new  ORMUtil().selectList(TbGoods.class,sql);
    }


}
